USE [TaskManager]
GO
/****** Object:  StoredProcedure [dbo].[Appointments_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Appointments_Delete]
        @Id                     int
AS
DELETE FROM [dbo].[Appointments]
      WHERE Id=@id

GO
/****** Object:  StoredProcedure [dbo].[Appointments_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Appointments_GetAll]
AS
BEGIN
        SELECT [Id]
                  ,[Title]
                  ,[Description]
                  ,[StartDate]
                  ,[EndDate]
                  ,[CreateDate]
                  ,[ModifyDate]
                  ,[CreateBy]
                  ,[ModifyBy]
          FROM [dbo].[Appointments]
END


GO
/****** Object:  StoredProcedure [dbo].[Appointments_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Appointments_GetById]
        @Id             int
AS
BEGIN
        SELECT [Id]
                  ,[Title]
                  ,[Description]
                  ,[StartDate]
                  ,[EndDate]
                  ,[CreateDate]
                  ,[ModifyDate]
                  ,[CreateBy]
                  ,[ModifyBy]
         FROM [dbo].[Appointments]
         WHERE Id=@Id
END



GO
/****** Object:  StoredProcedure [dbo].[Appointments_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Appointments_Insert]
                        (@Title                  nvarchar(200)
           ,@Description         nvarchar(500)
           ,@StartDate           datetime
           ,@EndDate             datetime
           ,@CreateDate          datetime
           ,@ModifyDate          datetime
           ,@CreateBy            int
           ,@ModifyBy            int
           ,@Id INT OUTPUT)
AS
BEGIN
        INSERT INTO [dbo].[Appointments]
                   ([Title]
                   ,[Description]
                   ,[StartDate]
                   ,[EndDate]
                   ,[CreateDate]
                   ,[ModifyDate]
                   ,[CreateBy]
                   ,[ModifyBy])
             VALUES
                   (@Title                      
                   ,@Description        
                   ,@StartDate          
                   ,@EndDate            
                   ,@CreateDate         
                   ,@ModifyDate         
                   ,@CreateBy           
                   ,@ModifyBy   )       
                   
                   SELECT @Id = @@IDENTITY
END



GO
/****** Object:  StoredProcedure [dbo].[Appointments_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Appointments_Update]
                   (@Id                          int
                   ,@Title                       nvarchar(200)
           ,@Description         nvarchar(500)
           ,@StartDate           datetime
           ,@EndDate             datetime
           ,@CreateDate          datetime
           ,@ModifyDate          datetime
           ,@CreateBy            int
           ,@ModifyBy            int)
AS
BEGIN
        UPDATE [dbo].[Appointments]
           SET [Title]                  = @Title                
              ,[Description]    = @Description
              ,[StartDate]              = @StartDate    
              ,[EndDate]                = @EndDate      
              ,[CreateDate]             = @CreateDate   
              ,[ModifyDate]             = @ModifyDate   
              ,[CreateBy]               = @CreateBy     
              ,[ModifyBy]               = @ModifyBy     
         WHERE Id=@Id
         DELETE FROM Attendees WHERE AppointmentId = @Id
END



GO
/****** Object:  StoredProcedure [dbo].[AssignTasks_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AssignTasks_Delete]
        @Id                                      int
AS
BEGIN
        DELETE FROM [dbo].[AssignTasks]
        WHERE Id=@Id
        
        -- DELETE report
        DELETE FROM Reports WHERE AssignTaskId = @Id
END

GO
/****** Object:  StoredProcedure [dbo].[AssignTasks_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AssignTasks_GetAll]
AS
BEGIN
        SELECT [Id]
                  ,[TaskId]
                  ,[UserId]
                  ,[Requirement]
                  ,[CompletedPercent]
                  ,[StartDate]
                  ,[EndDate]
                  ,[CreateDate]
                  ,[ModifyDate]
                  ,[CreateBy]
                  ,[ModifyBy]
          FROM [dbo].[AssignTasks]
END



GO
/****** Object:  StoredProcedure [dbo].[AssignTasks_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AssignTasks_GetById]
        @Id                     int
AS
BEGIN
        SELECT [Id]
                  ,[TaskId]
                  ,[UserId]
                  ,[Requirement]
                  ,[CompletedPercent]
                  ,[StartDate]
                  ,[EndDate]
                  ,[CreateDate]
                  ,[ModifyDate]
                  ,[CreateBy]
                  ,[ModifyBy]
        FROM [dbo].[AssignTasks]
        WHERE Id=@Id
END



GO
/****** Object:  StoredProcedure [dbo].[AssignTasks_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AssignTasks_Insert]
                  (@TaskId                               int
           ,@UserId                              int
           ,@Requirement                 nvarchar(500)
           ,@CompletedPercent    int
                   ,@StartDate                   datetime
                   ,@EndDate                     datetime
           ,@CreateDate                  datetime
           ,@ModifyDate                  datetime
           ,@CreateBy                    int
           ,@ModifyBy                    int)
AS
BEGIN
        INSERT INTO [dbo].[AssignTasks]
                   ([TaskId]
                   ,[UserId]
                   ,[Requirement]
                   ,[CompletedPercent]
                   ,[StartDate]
                           ,[EndDate]
                           ,[CreateDate]
                   ,[ModifyDate]
                   ,[CreateBy]
                   ,[ModifyBy])
             VALUES
                   (@TaskId                              
                   ,@UserId                              
                   ,@Requirement                 
                   ,@CompletedPercent
                           ,@StartDate
                           ,@EndDate    
                   ,@CreateDate                  
                   ,@ModifyDate                  
                   ,@CreateBy                    
                   ,@ModifyBy                    )
END



GO
/****** Object:  StoredProcedure [dbo].[AssignTasks_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AssignTasks_Update]
                        
                  ( @Id                                  int
                   ,@TaskId                              int
           ,@UserId                              int
           ,@Requirement                 nvarchar(500)
           ,@CompletedPercent    int
                   ,@StartDate                   datetime
                   ,@EndDate                     datetime
           ,@CreateDate                  datetime
           ,@ModifyDate                  datetime
           ,@CreateBy                    int
           ,@ModifyBy                    int)
AS
BEGIN
        UPDATE [dbo].[AssignTasks]
           SET [TaskId]                         = @TaskId                               
                  ,[UserId]                             = @UserId                               
                  ,[Requirement]                = @Requirement          
                  ,[CompletedPercent]   = @CompletedPercent     
                  ,[StartDate]                  = @StartDate
                  ,[EndDate]                    = @EndDate
                  ,[CreateDate]                 = @CreateDate                   
                  ,[ModifyDate]                 = @ModifyDate                   
                  ,[CreateBy]                   = @CreateBy                     
                  ,[ModifyBy]                   = @ModifyBy                     
         WHERE Id=@Id
END


GO
/****** Object:  StoredProcedure [dbo].[ChatMessage_UpdateIsRead]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessage_UpdateIsRead]
@ChatRoomId INT,
@ToUserId INT
AS
BEGIN
        UPDATE ChatMessages SET IsRead = 1 WHERE @ChatRoomId = RoomId AND ToUserId = @ToUserId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_CountUnreadMessage]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_CountUnreadMessage]
@ChatRoomId INT,
@ToUserId INT
AS
BEGIN
        SELECT COUNT(*) FROM ChatMessages WHERE RoomId = @ChatRoomId AND ToUserId = @ToUserId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_GetById]
@Id INT
AS
BEGIN
        SELECT  * FROM ChatMessages WHERE Id = @Id
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_GetByRoomId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_GetByRoomId]
@ChatRoomId INT
AS
BEGIN
        SELECT * FROM ChatMessages WHERE RoomId = @ChatRoomId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_GetHistoriesByTop]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_GetHistoriesByTop]
@ChatRoomId INT,
@LastMessageId BIGINT,
@Top INT
AS
BEGIN
        SELECT TOP(@Top) * FROM ChatMessages WHERE RoomId = @ChatRoomId AND @LastMessageId > Id ORDER BY Id DESC 
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_GetLastestByRoomId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_GetLastestByRoomId]
@ChatRoomId INT
AS
BEGIN
        SELECT TOP 1 * FROM ChatMessages WHERE RoomId = @ChatRoomId ORDER BY Id DESC
END

GO
/****** Object:  StoredProcedure [dbo].[ChatMessages_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatMessages_Insert]
@RoomId INT,
@FromUserId INT,
@ToUserId INT,
@Message NVARCHAR(1000),
@CreateDate DATETIME,
@CreateDateStamp BIGINT,
@IsRead BIT,
@Status INT,
@Id BIGINT OUTPUT
AS
BEGIN
        INSERT INTO [TaskManager].[dbo].[ChatMessages]
           ([RoomId]
           ,[Message]
           ,[FromUserId]
           ,[ToUserId]
           ,[CreateDate]
           ,[CreateDateStamp]
           ,[IsRead]
           ,[Status])
     VALUES
           (@RoomId
           ,@Message
           ,@FromUserId
           ,@ToUserId
           ,@CreateDate
           ,@CreateDateStamp
           ,@IsRead
           ,@Status)
     SELECT @Id = @@IDENTITY
END

GO
/****** Object:  StoredProcedure [dbo].[ChatRooms_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatRooms_GetById]
@RoomId INT
AS
BEGIN
        SELECT * FROM ChatRooms WHERE Id = @RoomId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatRooms_GetByUserId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatRooms_GetByUserId]
@UserId INT
AS
BEGIN
        SELECT * FROM ChatRooms WHERE FromUserId = @UserId OR ToUserId = @UserId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatRooms_GetByUsers]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*Chat proc*/

CREATE PROC [dbo].[ChatRooms_GetByUsers]
@FromUserId INT,
@ToUserId INT
AS
BEGIN
        SELECT * FROM ChatRooms WHERE FromUserId = @FromUserId AND ToUserId = @ToUserId
END

GO
/****** Object:  StoredProcedure [dbo].[ChatRooms_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatRooms_Insert]
@FromUserId INT,
@ToUserId INT,
@CreateDate DATETIME,
@Id int OUTPUT
AS
BEGIN
        INSERT INTO [ChatRooms]
           ([FromUserId]
           ,[ToUserId]
           ,[CreateDate])
     VALUES
           (@FromUserId
           ,@ToUserId
           ,@CreateDate)
    SELECT @Id = @@IDENTITY
END

GO
/****** Object:  StoredProcedure [dbo].[ChatRooms_SearchAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ChatRooms_SearchAll]
@FromDate DATETIME,
@ToDate DATETIME,
@UserId INT
AS
BEGIN
        SELECT * FROM ChatRooms WHERE (FromUserId = @UserId OR ToUserId = @UserId) AND CreateDate BETWEEN @FromDate AND @ToDate 
END

GO
/****** Object:  StoredProcedure [dbo].[Departments_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Departments_Delete]

        @Id             int

AS

BEGIN

        DELETE FROM [dbo].[Departments]

        WHERE Id=@Id

END










GO
/****** Object:  StoredProcedure [dbo].[Departments_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Departments_GetAll]

AS

BEGIN

        SELECT [Id]

                  ,[Name]

                  ,[UserId]

                  ,[CreateDate]

                  ,[ModifyDate]

                  ,[CreateBy]

                  ,[ModifyBy]

          FROM [dbo].[Departments]

END










GO
/****** Object:  StoredProcedure [dbo].[Departments_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Departments_GetById]

        @DepartmentId int

AS

BEGIN

        SELECT [Id]

                  ,[Name]

                  ,[UserId]

                  ,[CreateDate]

                  ,[ModifyDate]

                  ,[CreateBy]

                  ,[ModifyBy]

        FROM [dbo].[Departments]

        WHERE Id=@DepartmentId

END










GO
/****** Object:  StoredProcedure [dbo].[Departments_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Departments_Insert]

                    @Name                nvarchar(100)  

           ,@UserId              int  

           ,@CreateDate  datetime  

           ,@ModifyDate  datetime  

           ,@CreateBy    int  

           ,@ModifyBy    int  

AS

BEGIN

        INSERT INTO [dbo].[Departments]

                           ([Name]

                           ,[UserId]

                           ,[CreateDate]

                           ,[ModifyDate]

                           ,[CreateBy]

                           ,[ModifyBy])

                 VALUES

                           (@Name                

                           ,@UserId              

                           ,@CreateDate  

                           ,@ModifyDate  

                           ,@CreateBy    

                           ,@ModifyBy)

END 




GO
/****** Object:  StoredProcedure [dbo].[Departments_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Departments_Update]

                        @Id                      int

                   ,@Name                nvarchar(100)  

           ,@UserId              int  

           ,@CreateDate  datetime  

           ,@ModifyDate  datetime  

           ,@CreateBy    int  

           ,@ModifyBy    int 

AS

BEGIN

        UPDATE [dbo].[Departments]

           SET [Name]           = @Name

                  ,[UserId]             = @UserId

                  ,[CreateDate] = @CreateDate

                  ,[ModifyDate] = @ModifyDate

                  ,[CreateBy]   = @CreateBy

                  ,[ModifyBy]   = @ModifyBy

        WHERE Id=@Id

END










GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_DeleteByReportId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_DeleteByReportId]

        @ReportId               int

AS

BEGIN

        DELETE FROM [dbo].[ReportDocs]

        WHERE ReportId=@ReportId

END

GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_GetAll]

AS

BEGIN

        SELECT [ReportId]

                  ,[DocumentId]

        FROM [dbo].[ReportDocs]

END





-------------




GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_GetByDocumentId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_GetByDocumentId]

        @DocumentId int

AS

BEGIN

  SELECT [ReportId]

      ,[DocumentId]

  FROM [dbo].[ReportDocs]

  WHERE DocumentId=@DocumentId

END



-------------




GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_GetByReportId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_GetByReportId]

        @ReportId int

AS

BEGIN

  SELECT [ReportId]

      ,[DocumentId]

  FROM [dbo].[ReportDocs]

  WHERE ReportId=@ReportId

END

------------




GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_Insert]

                   (@ReportId            int

           ,@DocumentId          int)

AS

INSERT INTO [dbo].[ReportDocs]

           ([ReportId]

           ,[DocumentId])

     VALUES

           (@ReportId   

           ,@DocumentId)


GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_UpdateByDocumentId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_UpdateByDocumentId]

        @ReportId               int,

        @DocumentId             int

AS

BEGIN

        UPDATE [dbo].[ReportDocs]

           SET [ReportId] = @ReportId

        WHERE DocumentId=@DocumentId

END

-----------




GO
/****** Object:  StoredProcedure [dbo].[ReportDocs_UpdateByReportId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ReportDocs_UpdateByReportId]

        @ReportId               int,

        @DocumentId             int

AS

BEGIN

        UPDATE [dbo].[ReportDocs]

           SET [DocumentId] = @DocumentId

        WHERE ReportId=@ReportId

END

----------




GO
/****** Object:  StoredProcedure [dbo].[Reports_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Reports_Delete]

        @Id             int

AS

BEGIN

        DELETE FROM [dbo].[Reports]

        WHERE Id=@Id

END

----------




GO
/****** Object:  StoredProcedure [dbo].[Reports_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Reports_GetAll]
AS
BEGIN
        SELECT [Id]
                                  ,[AssignTaskId]
                  ,[TaskId]
                  ,[ReportDate]
                  ,[ReportResult]
                  ,[NextTask]
                                  ,[CompletedPercent]
                  ,[UserReport]
                                  ,[Comment]
                                  ,[CommentBy]
                                  ,[CommentDate]
                  ,[CreateDate]
                  ,[ModifyDate]
                  ,[CreateBy]
                  ,[ModifyBy]
        FROM [dbo].[Reports]
END
-------------




GO
/****** Object:  StoredProcedure [dbo].[Reports_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Reports_GetById]
        @Id             int
AS
BEGIN
        SELECT [Id]
        
                                  ,[AssignTaskId]
                                  ,[TaskId]
                  ,[ReportDate]
                  ,[ReportResult]
                  ,[NextTask]
                  ,[CompletedPercent]
                  ,[UserReport]
                                  ,[Comment]
                                  ,[CommentBy]
                                  ,[CommentDate]
                  ,[CreateDate]
                  ,[ModifyDate]
                  ,[CreateBy]
                  ,[ModifyBy]
          FROM [dbo].[Reports]
          WHERE Id=@Id
END

GO
/****** Object:  StoredProcedure [dbo].[Reports_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Reports_Insert]
                   (
            @AssignTaskId                             int
           ,@TaskId                             int
           ,@ReportDate                 datetime
           ,@ReportResult               nvarchar(500)
           ,@NextTask                   nvarchar(500)
           ,@CompletedPercent                   int
           ,@UserReport                 int
                   ,@Comment                                    nvarchar(500)
                   ,@CommentBy                                  int
                   ,@CommentDate                                datetime
           ,@CreateDate                 datetime
           ,@ModifyDate                 datetime
           ,@CreateBy                   int
           ,@ModifyBy                   int
           ,@Id INT OUTPUT)
AS
BEGIN
        INSERT INTO [dbo].[Reports]
                           (
                           [AssignTaskId]
                           ,[TaskId]
                           ,[ReportDate]
                           ,[ReportResult]
                           ,[NextTask]
                           ,[CompletedPercent]
                           ,[UserReport]
                                                   ,[Comment]
                                                   ,[CommentBy]
                                                   ,[CommentDate]
                           ,[CreateDate]
                           ,[ModifyDate]
                           ,[CreateBy]
                           ,[ModifyBy])
                 VALUES
                           (
                           @AssignTaskId
                           ,@TaskId                             
                           ,@ReportDate                 
                           ,@ReportResult               
                           ,@NextTask                   
                           ,@CompletedPercent   
                           ,@UserReport 
                                                   ,@Comment
                                                   ,@CommentBy
                                                   ,@CommentDate
                           ,@CreateDate                 
                           ,@ModifyDate                 
                           ,@CreateBy                   
                           ,@ModifyBy           )       
                           
                           SELECT @Id = @@IDENTITY

END





--------------




GO
/****** Object:  StoredProcedure [dbo].[Reports_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Reports_Update]

                   (@Id                         int
                   ,@AssignTaskId                             int
           ,@TaskId                     int
           ,@ReportDate                 datetime
           ,@ReportResult               nvarchar(500)
           ,@NextTask                   nvarchar(500)
           ,@CompletedPercent                   int
           ,@UserReport                 int
                   ,@Comment                                    nvarchar(500)
                   ,@CommentBy                                  int
                   ,@CommentDate                                datetime
           ,@CreateDate                 datetime
           ,@ModifyDate                 datetime
           ,@CreateBy                   int
           ,@ModifyBy                   int)
AS
BEGIN
        UPDATE [dbo].[Reports]                                          
           SET     [TaskId]                     = @TaskId 
                                  ,[AssignTaskId]                                       = @AssignTaskId
                  ,[ReportDate]                 = @ReportDate                   
                  ,[ReportResult]               = @ReportResult         
                  ,[NextTask]                   = @NextTask                     
                  ,[CompletedPercent]                   = @CompletedPercent     
                  ,[UserReport]                 = @UserReport                   
                                  ,[Comment]                                    = @Comment
                                  ,[CommentBy]                                  = @CommentBy
                                  ,[CommentDate]                                = @CommentDate
                  ,[CreateDate]                 = @CreateDate                   
                  ,[ModifyDate]                 = @ModifyDate                   
                  ,[CreateBy]                   = @CreateBy                     
                  ,[ModifyBy]                   = @ModifyBy                     
         WHERE Id=@Id
END



GO
/****** Object:  StoredProcedure [dbo].[TaskDocs_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[TaskDocs_Delete]

        @TaskId int

AS

BEGIN

        DELETE FROM [dbo].[TaskDocs]

        WHERE TaskId=@TaskId

END

--------------




GO
/****** Object:  StoredProcedure [dbo].[TaskDocs_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[TaskDocs_GetAll]

AS

BEGIN

        SELECT [TaskId]

                  ,[DocumentId]

          FROM [dbo].[TaskDocs]

END





------------




GO
/****** Object:  StoredProcedure [dbo].[TaskDocs_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[TaskDocs_Insert]

        @TaskId         int,

        @DocumentId int

AS

BEGIN

        INSERT INTO [dbo].[TaskDocs]

                           ([TaskId]

                           ,[DocumentId])

        VALUES

                           (@TaskId

                           ,@DocumentId)

END



--------




GO
/****** Object:  StoredProcedure [dbo].[TaskDocs_UpdateByTaskId]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[TaskDocs_UpdateByTaskId]

        @TaskId         int,

        @DocumentId int

AS

BEGIN

        UPDATE [dbo].[TaskDocs]

           SET [TaskId]         = @TaskId

                  ,[DocumentId] = @DocumentId

        WHERE TaskId=@TaskId

END





----------




GO
/****** Object:  StoredProcedure [dbo].[Tasks_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Tasks_Delete]

        @Id             int

AS

BEGIN

                DECLARE @AssignTaskId INT

                DECLARE task_cursor CURSOR FOR 
                SELECT Id
                FROM AssignTasks
                WHERE TaskId = @Id

                OPEN task_cursor
                FETCH NEXT FROM task_cursor 
                INTO @AssignTaskId

                WHILE @@FETCH_STATUS = 0
                BEGIN
                        
                        -- Delete report
                        DELETE FROM Reports WHERE AssignTaskId = @AssignTaskId
                        
                        -- Delete assign task
                        DELETE FROM AssignTasks WHERE Id = @AssignTaskId
                        
                        FETCH NEXT FROM task_cursor 
                        INTO @AssignTaskId
                END 
                CLOSE task_cursor
                DEALLOCATE task_cursor

                -- Delete Task
        DELETE FROM [dbo].[Tasks]
        WHERE Id=@Id

END

----------




GO
/****** Object:  StoredProcedure [dbo].[Tasks_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Tasks_GetAll]
AS
SELECT [Id]
      ,[Name]
      ,[Leader]
      ,[Priority]
      ,[Description]
      ,[StartDate]
      ,[EndDate]
      ,[CreateDate]
      ,[ModifyDate]
      ,[CreateBy]
      ,[ModifyBy]
  FROM [dbo].[Tasks]

GO
/****** Object:  StoredProcedure [dbo].[Tasks_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Tasks_GetById]
        @Id             int
AS
BEGIN
        SELECT          [Id]
                  ,[Name]
                                  ,[Leader]
                                  ,[Priority]
                  ,[Description]
                                  ,[StartDate]
                                  ,[EndDate]
                                  ,[CreateDate]
                  ,[ModifyDate]
                  ,[CreateBy]
                  ,[ModifyBy]
        FROM [dbo].[Tasks]
        WHERE Id=@Id
END




GO
/****** Object:  StoredProcedure [dbo].[Tasks_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Tasks_Insert]
                           (@Name        nvarchar(200)
                                                   ,@Leader              int
                                                   ,@Priority    int
                           ,@Description nvarchar(500)
                                                   ,@StartDate   Datetime
                                                   ,@EndDate     Datetime
                           ,@CreateDate  datetime
                           ,@ModifyDate  datetime
                           ,@CreateBy    int
                           ,@ModifyBy    int
                           ,@Id INT OUTPUT
                           )

AS

BEGIN

        INSERT INTO [dbo].[Tasks]

                           ([Name]
                                                   ,[Leader]
                                                   ,[Priority]
                           ,[Description]
                                                   ,[StartDate]
                                                   ,[EndDate]
                           ,[CreateDate]
                           ,[ModifyDate]
                           ,[CreateBy]
                           ,[ModifyBy])

                 VALUES

                           (@Name                
                                                   ,@Leader
                                                   ,@Priority
                           ,@Description 
                                                   ,@StartDate
                                                   ,@EndDate
                           ,@CreateDate  
                           ,@ModifyDate  
                           ,@CreateBy    
                           ,@ModifyBy    )
         SELECT @Id = @@IDENTITY
END




GO
/****** Object:  StoredProcedure [dbo].[Tasks_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Tasks_Update]

                           (@Id                  int
                           ,@Name                nvarchar(200)
                                                   ,@Leader              int
                                                   ,@Priority    int
                           ,@Description nvarchar(500)
                                                   ,@StartDate   Datetime
                                                   ,@EndDate     Datetime
                           ,@CreateDate  datetime
                           ,@ModifyDate  datetime
                           ,@CreateBy    int
                           ,@ModifyBy    int)

AS
BEGIN
        UPDATE [dbo].[Tasks]
           SET          [Name]       = @Name                        
                                  ,[Leader]              = @Leader
                                  ,[Priority]    = @Priority
                  ,[Description] = @Description         
                                  ,[StartDate]   = @StartDate
                                  ,[EndDate]     = @EndDate
                  ,[CreateDate]  = @CreateDate
                  ,[ModifyDate]  = @ModifyDate  
                  ,[CreateBy]    = @CreateBy    
                  ,[ModifyBy]    = @ModifyBy    
         WHERE Id=@Id
END

-----------------




GO
/****** Object:  StoredProcedure [dbo].[Users_Delete]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_Delete]

        @Id     int

AS

BEGIN

        DELETE FROM [dbo].[Users]

        WHERE Id=@Id

END










GO
/****** Object:  StoredProcedure [dbo].[Users_GetAll]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_GetAll]

AS

BEGIN

        SELECT [Id]

                  ,[UserName]

                  ,[Password]

                  ,[FirstName]

                  ,[LastName]

                  ,[Address]

                  ,[DateOfBirth]

                  ,[Gender]

                  ,[DepartmentId]
                                  ,[Email]
                                  ,[Mission]
                  ,[Avatar]

                  ,[IsActive]

                  ,[IsAdmin]
                                  ,[IsManager]
                  ,[CreateDate]

                  ,[ModifyDate]

                  ,[CreateBy]

                  ,[ModifyBy]

          FROM [dbo].[Users]

END










GO
/****** Object:  StoredProcedure [dbo].[Users_GetById]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_GetById]

        @Id             int

AS

BEGIN

        SELECT [Id]

                  ,[UserName]

                  ,[Password]

                  ,[FirstName]

                  ,[LastName]

                  ,[Address]

                  ,[DateOfBirth]

                  ,[Gender]

                  ,[DepartmentId]
                                  ,[Email]
                                  ,[Mission]
                  ,[Avatar]

                  ,[IsActive]

                  ,[IsAdmin]
                                  ,[IsManager]
                  ,[CreateDate]

                  ,[ModifyDate]

                  ,[CreateBy]

                  ,[ModifyBy]

        FROM [dbo].[Users]

        WHERE Id=@Id

END










GO
/****** Object:  StoredProcedure [dbo].[Users_Insert]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_Insert]

            (@UserName            varchar(100)  

           ,@Password            varchar(100)  

           ,@FirstName           nvarchar(100)  

           ,@LastName            nvarchar(100)  

           ,@Address             nvarchar(500)  

           ,@DateOfBirth         date  

           ,@Gender                      bit  

           ,@DepartmentId        int  
                   ,@Email                               nvarchar(50)
                   ,@Mission                     nvarchar(100)
           ,@Avatar              nvarchar(200)  

           ,@IsActive            bit  

           ,@IsAdmin             bit  
                   ,@IsManager               bit
           ,@CreateDate          datetime  

           ,@ModifyDate          datetime  

           ,@CreateBy            int  

           ,@ModifyBy            int  )

AS

BEGIN

        INSERT INTO [dbo].[Users]

                           ([UserName]

                           ,[Password]

                           ,[FirstName]

                           ,[LastName]

                           ,[Address]

                           ,[DateOfBirth]

                           ,[Gender]

                           ,[DepartmentId]
                                                   ,[Email]
                                                   ,[Mission]
                                                   ,[Avatar]

                           ,[IsActive]

                           ,[IsAdmin]
                                                   ,[IsManager]
                           ,[CreateDate]

                           ,[ModifyDate]

                           ,[CreateBy]

                           ,[ModifyBy])

                 VALUES

                           (@UserName           

                           ,@Password           

                           ,@FirstName          

                           ,@LastName           

                           ,@Address            

                           ,@DateOfBirth        

                           ,@Gender                     

                           ,@DepartmentId       
                                                   ,@Email
                                                   ,@Mission
                           ,@Avatar                     

                           ,@IsActive           

                           ,@IsAdmin            
                                                   ,@IsManager
                           ,@CreateDate         

                           ,@ModifyDate         

                           ,@CreateBy           

                           ,@ModifyBy           )

END




GO
/****** Object:  StoredProcedure [dbo].[Users_Login]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Users_Login]
@UserName VARCHAR(100),
@Password VARCHAR(100)
AS
BEGIN
        SELECT * FROM Users WHERE LOWER(UserName) = @UserName AND Password = @Password AND IsActive = 1
END

GO
/****** Object:  StoredProcedure [dbo].[Users_Search]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_Search]
@UserName VARCHAR(100),
@FullName NVARCHAR(200),
@DepartmentId INT
AS
BEGIN
        DECLARE @sql NVARCHAR(MAX) = ''
        DECLARE @where NVARCHAR(MAX) = ' WHERE 1 = 1 '
        SET @sql += 'SELECT * FROM Users '
        IF      @DepartmentId != 0
        BEGIN
                SET @where += ' AND DepartmentId = ' + convert(varchar, @DepartmentId)
        END
        
        IF @UserName IS NOT NULL
        BEGIN
                SET @where += ' AND LOWER(UserName) Like ''%' + @UserName + '%'''
        END
        
        IF @FullName IS NOT NULL
        BEGIN
                SET @where += ' AND LOWER(FirstName + LastName) Like ''%' + @FullName + '%'''
        END
        
        EXEC (@sql + @where)
        
END

GO
/****** Object:  StoredProcedure [dbo].[Users_Update]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_Update]

                   (@Id                          int

                   ,@UserName            varchar(100)  

           ,@Password            varchar(100)  

           ,@FirstName           nvarchar(100)  

           ,@LastName            nvarchar(100)  

           ,@Address             nvarchar(500)  

           ,@DateOfBirth         date  

           ,@Gender                      bit  

           ,@DepartmentId        int  
                   ,@Email                               nvarchar(50)
                   ,@Mission                     nvarchar(100)
           ,@Avatar                      nvarchar(200)  

           ,@IsActive            bit  

           ,@IsAdmin             bit  
                   ,@IsManager                   bit
           ,@CreateDate          datetime  

           ,@ModifyDate          datetime  

           ,@CreateBy            int  

           ,@ModifyBy            int  )

AS

BEGIN

        UPDATE [dbo].[Users]

           SET [UserName]               = @UserName     

                  ,[Password]           = @Password     

                  ,[FirstName]          = @FirstName    

                  ,[LastName]           = @LastName     

                  ,[Address]            = @Address      

                  ,[DateOfBirth]        = @DateOfBirth

                  ,[Gender]                     = @Gender               

                  ,[DepartmentId]       = @DepartmentId
                                  ,[Email]                              = @Email 
                                  ,[Mission]                    = @Mission
                  ,[Avatar]                     = @Avatar               

                  ,[IsActive]           = @IsActive     

                  ,[IsAdmin]            = @IsAdmin      
                                  ,[IsManager]                  = @IsManager
                  ,[CreateDate]         = @CreateDate   

                  ,[ModifyDate]         = @ModifyDate   

                  ,[CreateBy]           = @CreateBy     

                  ,[ModifyBy]           = @ModifyBy     

         WHERE Id=@Id

END











GO
/****** Object:  StoredProcedure [dbo].[Users_UpdatePassword]    Script Date: 1/22/2015 11:17:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Users_UpdatePassword]

        @Id                             int,

        @Password               varchar(100)

AS

BEGIN

        UPDATE [dbo].[Users]

           SET [Password] = @Password

        WHERE Id=@Id

END






GO
CREATE PROC Departments_GetByUserId
        @UserId         int
AS
BEGIN
        SELECT [Id]
                  ,[Name]
                  ,[UserId]
                  ,[CreateDate]
                  ,[ModifyDate]
                  ,[CreateBy]
                  ,[ModifyBy]
        FROM [dbo].[Departments]
        WHERE UserId=@UserId
END
GO

CREATE PROC [dbo].[Tasks_Search]
        @Name                   VARCHAR(200),
        @Leader                 INT,
        @StartDate              DATETIME,
        @EndDate                DATETIME
AS
BEGIN
        DECLARE @Sql    NVARCHAR(4000)
    DECLARE     @Where  NVARCHAR(1000)
    IF  ((@StartDate IS NOT NULL) AND (@EndDate IS NOT NULL) AND (@StartDate <= @EndDate))
        BEGIN
                SET @Where      =                        ' AND StartDate >= ''' + CONVERT(VARCHAR, @StartDate, 121) + ''''
                SET @Where      =       @Where + ' AND EndDate <= '''   + CONVERT(VARCHAR, @EndDate, 121) + ''''
        END
                
    
        IF      (@Name IS NOT NULL AND @Name != '')
                SET     @Where  =       @Where + ' AND Name LIKE ''%' + @Name + '%'''
        IF      (@Leader > 0)
                SET     @Where  =       @Where + ' AND Leader = ' +     CONVERT(VARCHAR, @Leader)
        
        SET             @Sql    = 'SELECT  [Id]
                                                          ,[Name]
                                                          ,[Leader]
                                                          ,[Priority]
                                                          ,[Description]
                                                          ,[StartDate]
                                                          ,[EndDate]
                                                          ,[CreateDate]
                                                          ,[ModifyDate]
                                                          ,[CreateBy]
                                                          ,[ModifyBy]
                                                  FROM [dbo].[Tasks]
                                                WHERE           0       =       0
                                                ' +     @Where
        EXECUTE (@Sql)
END
GO
-----add Date 25/01/2015 by manhlt
CREATE PROC [dbo].[Departments_CheckExitUserId]
        @UserId         int
AS
BEGIN
        SELECT *
        FROM [dbo].[Departments]
        WHERE UserId=@UserId
END
GO

CREATE PROC [AssignTasks_GetByTaskId]
@TaskId INT
AS
BEGIN
        SELECT * FROM AssignTasks WHERE TaskId = @TaskId
END
GO

CREATE PROC [AssignTasks_UpdateCompleted]
@Id INT,
@CompletedPercent INT
AS
BEGIN 
        UPDATE AssignTasks SET CompletedPercent = @CompletedPercent WHERE @Id = Id
END
GO

CREATE PROC Users_GetByDepartmentId
@DepartmentId INT
AS 
BEGIN
        SELECT * FROM Users WHERE DepartmentId = @DepartmentId AND IsActive = 1
END
GO

CREATE PROC Documents_Insert
@Name NVARCHAR(200),
@FilePath NVARCHAR(500),
@Id INT OUTPUT
AS
BEGIN
INSERT INTO [dbo].[Documents]
           ([Name]
           ,[FilePath])
     VALUES
           (@Name
           ,@FilePath)
     SELECT @Id = @@IDENTITY
END
GO

CREATE PROC Documents_GetByIds
@Ids VARCHAR(2000)
AS
BEGIN
        EXEC('SELECT * FROM Documents WHERE Id IN (' +  @Ids + ')')
END
GO

create PROC Appointments_GetByUserId
@UserId INT
AS
BEGIN
        SELECT A.* 
        FROM Appointments as A
        WHERE A.CreateBy = @UserId 
                  OR 0 < (SELECT COUNT(*) FROM Attendees WHERE AppointmentId = A.Id AND UserId = @UserId)
                  and not exists (select 1 from AppointmentComments ac where ac.AppointmentId = A.Id and CommentType = 1)
END
GO

CREATE PROC Attendees_Insert
        @AppointmentId INT,
        @UserId INT
AS
BEGIN
INSERT INTO .[dbo].[Attendees]
           ([AppointmentId]
           ,[UserId])
     VALUES
           (@AppointmentId
           ,@UserId)
END
GO

CREATE PROC Attendees_GetByAppointmentId
        @AppointmentId INT
AS
BEGIN
        SELECT u.* FROM Attendees as a LEFT JOIN Users as u ON a.UserId = u.Id WHERE AppointmentId  = @AppointmentId
END
GO

CREATE PROC Reports_OverDues
        @DepartmentId INT,
        @Date VARCHAR(200)
AS
BEGIN
        SELECT * FROM Users WHERE DepartmentId = @DepartmentId AND Id NOT IN (SELECT UserReport FROM Reports WHERE CONVERT(VARCHAR(24),CreateDate,103) = @Date )
END

Go

CREATE PROC Attendees_Delete
        @AppointmentId INT
AS
BEGIN
        DELETE FROM Attendees WHERE AppointmentId = @AppointmentId
END
GO

CREATE PROC Reports_GetByUserReportAndReportDate
        @UserReport int,
        @Date VARCHAR(200)
AS
SELECT [Id]
      ,[AssignTaskId]
      ,[TaskId]
      ,[ReportDate]
      ,[ReportResult]
      ,[NextTask]
      ,[CompletedPercent]
      ,[UserReport]
      ,[Comment]
      ,[CommentBy]
      ,[CommentDate]
      ,[CreateDate]
      ,[ModifyDate]
      ,[CreateBy]
      ,[ModifyBy]
 FROM [dbo].[Reports]
 WHERE UserReport=@UserReport
   AND CONVERT(VARCHAR(24),ReportDate,103) = @Date
GO

CREATE PROC [dbo].[Reports_Search]
        @UserId                 INT,
        @StartDate              DATETIME,
        @EndDate                DATETIME
AS
BEGIN
        DECLARE @Sql    NVARCHAR(4000)
    DECLARE     @Where  NVARCHAR(1000)
    IF  ((@StartDate IS NOT NULL) AND (@EndDate IS NOT NULL) AND (@StartDate <= @EndDate))
        BEGIN
                SET @Where      =                        ' AND ReportDate >= '''        + CONVERT(VARCHAR, @StartDate, 121) + ''''
                SET @Where      =       @Where + ' AND ReportDate <= '''        + CONVERT(VARCHAR, @EndDate, 121) + ''''
        END

        IF      (@UserId > 0)
                SET     @Where  =       @Where + ' AND UserReport = ' + CONVERT(VARCHAR, @UserId)
        
        SET             @Sql    = 'SELECT  *
                                                  FROM [dbo].[Reports]
                                                WHERE           0       =       0
                                                ' +     @Where
        EXECUTE (@Sql)
END
GO
CREATE PROC [dbo].[AssignTasks_Search]
        @Requirement            VARCHAR(200),
        @UserId                         INT,
        @StartDate              DATETIME,
        @EndDate                DATETIME
AS
BEGIN
        DECLARE @Sql    NVARCHAR(4000)
    DECLARE     @Where  NVARCHAR(1000)
    IF  ((@StartDate IS NOT NULL) AND (@EndDate IS NOT NULL) AND (@StartDate <= @EndDate))
        BEGIN
                SET @Where      =       ' AND CreateDate >= ''' + CONVERT(VARCHAR, @StartDate, 121) + ''''
                SET @Where      =       @Where + ' AND CreateDate <= '''   + CONVERT(VARCHAR, @EndDate, 121) + ''''
        END
                
    
        IF      (@Requirement IS NOT NULL AND @Requirement != '')
                SET     @Where  =       @Where + ' AND Requirement LIKE ''%' + @Requirement + '%'''
        IF      (@UserId         > 0)
                SET     @Where  =       @Where + ' AND UserId = ' +    CONVERT(VARCHAR, @UserId)        
        SET             @Sql    = 'SELECT  [Id]
                                                                                  ,[TaskId]
                                                                                  ,[UserId]
                                                                                  ,[Requirement]
                                                                                  ,[CompletedPercent]
                                                                                  ,[StartDate]
                                                                                  ,[EndDate]
                                                                                  ,[CreateDate]
                                                                                  ,[ModifyDate]
                                                                                  ,[CreateBy]
                                                                                  ,[ModifyBy]
                                                                          FROM [dbo].[AssignTasks]              
                                      WHERE           0       =       0
                                                ' +     @Where
        EXECUTE (@Sql)
END

GO


CREATE PROC Alerts_GetByUserId
        @UserId INT
AS
BEGIN
        SELECT * FROM Alerts WHERE UserId = @UserId AND IsRead = 0
END
GO

CREATE PROC Alerts_Insert
        @Message                                NVARCHAR(1000),
        @UserId                         INT,
        @ReferenceId                    INT,
        @TypeId                         INT,
        @IsRead                         BIT
AS
BEGIN
        INSERT INTO Alerts VALUES (@Message, @UserId, @ReferenceId, @TypeId, @IsRead)
END
GO

CREATE PROC Alerts_Delete
        @Id INT
        AS
        BEGIN 
                DELETE FROM Alerts WHERE Id = @Id
        END
GO

CREATE PROC Alerts_DeleteByType
        @TypeId INT,
        @ReferenceId INT
        AS
        BEGIN 
                DELETE FROM Alerts WHERE ReferenceId = @ReferenceId AND TypeId = @TypeId
        END
GO


CREATE PROC Projects_SellectAll
        AS
        BEGIN
                SELECT [Id]
                          ,[Name]
                          ,[Description]
                          ,[StartDate]
                          ,[EndDate]
                          ,[CreateDate]
                          ,[ModifyDate]
                          ,[CreateBy]
                          ,[ModifyBy]
                FROM [dbo].[Projects]
        END
GO


CREATE PROC Projects_Insert
                                   (@Name                       nvarchar(200)
                                   ,@Description        nvarchar(500)
                                   ,@StartDate          datetime
                                   ,@EndDate            datetime
                                   ,@CreateDate         datetime
                                   ,@ModifyDate         datetime
                                   ,@CreateBy           int
                                   ,@ModifyBy           int)
        AS
        BEGIN
                INSERT INTO [dbo].[Projects]
                                   ([Name]
                                   ,[Description]
                                   ,[StartDate]
                                   ,[EndDate]
                                   ,[CreateDate]
                                   ,[ModifyDate]
                                   ,[CreateBy]
                                   ,[ModifyBy])
                         VALUES
                                   (@Name
                                   ,@Description
                                   ,@StartDate
                                   ,@EndDate
                                   ,@CreateDate
                                   ,@ModifyDate
                                   ,@CreateBy
                                   ,@ModifyBy)
        END
GO


CREATE PROC Projects_Update
                                        (@Id                            int
                                   ,@Name                       nvarchar(200)
                                   ,@Description        nvarchar(500)
                                   ,@StartDate          datetime
                                   ,@EndDate            datetime
                                   ,@CreateDate         datetime
                                   ,@ModifyDate         datetime
                                   ,@CreateBy           int
                                   ,@ModifyBy           int)    
        AS
        BEGIN
                UPDATE [dbo].[Projects]
                   SET [Name]                   = @Name
                          ,[Description]        = @Description
                          ,[StartDate]          = @StartDate
                          ,[EndDate]            = @EndDate
                          ,[CreateDate]         = @CreateDate
                          ,[ModifyDate]         = @ModifyDate
                          ,[CreateBy]           = @CreateBy
                          ,[ModifyBy]           = @ModifyBy
                WHERE Id=@Id
        END
GO

CREATE PROC Projects_Delete
        @Id             int
        AS
        BEGIN
                DELETE FROM [dbo].[Projects]
                WHERE Id=@Id
        END
GO


CREATE PROC [dbo].[Projects_Search]
        @Name                   VARCHAR(200),
        @StartDate              DATETIME,
        @EndDate                DATETIME
AS
BEGIN
        DECLARE @Sql    NVARCHAR(4000)
    DECLARE     @Where  NVARCHAR(1000)
    IF  ((@StartDate IS NOT NULL) AND (@EndDate IS NOT NULL) AND (@StartDate <= @EndDate))
        BEGIN
                SET @Where      =                        ' AND StartDate >= ''' + CONVERT(VARCHAR, @StartDate, 121) + ''''
                SET @Where      =       @Where + ' AND EndDate <= '''   + CONVERT(VARCHAR, @EndDate, 121) + ''''
        END
                
    
        IF      (@Name IS NOT NULL AND @Name != '')
                SET     @Where  =       @Where + ' AND Name LIKE ''%' + @Name + '%'''
        
        SET             @Sql    = 'SELECT [Id]
                                                          ,[Name]
                                                          ,[Description]
                                                          ,[StartDate]
                                                          ,[EndDate]
                                                          ,[CreateDate]
                                                          ,[ModifyDate]
                                                          ,[CreateBy]
                                                          ,[ModifyBy]
                                                  FROM [dbo].[Projects]
                                                WHERE           0       =       0
                                                ' +     @Where
        EXECUTE (@Sql)
END
Go
CREATE PROC Projects_GetById
	@Id			int
	AS
	BEGIN
		SELECT [Id]
			  ,[Name]
			  ,[Description]
			  ,[StartDate]
			  ,[EndDate]
			  ,[CreateDate]
			  ,[ModifyDate]
			  ,[CreateBy]
			  ,[ModifyBy]
		FROM [dbo].[Projects]
		WHERE Id=@Id
	END
GO

create proc AppointmentComments_GetAppId
@Id int
as 
begin
        select * from AppointmentComments where AppointmentId = @Id
end
go

create proc AppointmentComment_CheckExisted
@AppId int,
@UserId int
as 
begin
        select * from AppointmentComments where AppointmentId = @AppId and CreateBy = @UserId
end
go

create proc AppointmentComment_CountAccepted
@AppId int
as
begin 
        select COUNT(*) as total from AppointmentComments where AppointmentId = @AppId and CommentType = 0
end
GO

CREATE PROC [dbo].[AppointmentComments_Insert]
                 ( @CommentType          int
                 ,@AppointmentId        int                             
           ,@Comments            nvarchar(500)
           ,@CreateDate          datetime
           ,@ModifyDate          datetime
           ,@CreateBy            int
           ,@ModifyBy            int
           ,@Id bigint OUTPUT)
AS
BEGIN
        INSERT INTO [dbo].[AppointmentComments]
           (CommentType
           ,AppointmentId
           ,Comments
           ,[CreateDate]
           ,[ModifyDate]
           ,[CreateBy]
           ,[ModifyBy])
 VALUES
           (@CommentType                        
           ,@AppointmentId      
           ,@Comments           
           ,@CreateDate         
           ,@ModifyDate         
           ,@CreateBy           
           ,@ModifyBy   )       
           
           SELECT @Id = @@IDENTITY
           
END
Go